package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jndi.JndiFactory;
import models.Picture;

public class PictureDaoImpl implements PictureDao {

	final JndiFactory jndi = JndiFactory.getInstance();

	public List<Picture> list(Long camId, String date) {

		List<Picture> pictures = new ArrayList<>();
		Connection connection = null;

		try {
			connection = jndi.getConnection("jdbc/waiDatenbank");

			PreparedStatement ps = connection
					.prepareStatement("SELECT id, camid, picpath, picthumbpath, pictimestamp FROM pictures WHERE camid=? AND pg_catalog.date(pictimestamp)='" + date + "' ORDER BY pictimestamp");
			ps.setLong(1, camId);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Picture picture = new Picture();
				picture.setId(rs.getLong("id"));
				picture.setCamid(rs.getLong("camid"));
				picture.setPicpath(rs.getString("picpath"));
				picture.setPicthumbpath(rs.getString("picthumbpath"));
				picture.setPictimestamp(rs.getTimestamp("pictimestamp"));
				pictures.add(picture);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JndiFactory.closeConnection(connection);
		}

		return pictures;
	}

	@Override
	public boolean save(Picture picture) {

		Connection connection = null;

		try {
			connection = jndi.getConnection("jdbc/waiDatenbank");

			PreparedStatement ps = connection
					.prepareStatement("INSERT INTO pictures (camid, picpath, picthumbpath, pictimestamp) "
							+ "VALUES (?, ?, ?, ?)");

			ps.setLong(1, picture.getCamid());
			ps.setString(2, picture.getPicpath());
			ps.setString(3, picture.getPicthumbpath());
			ps.setTimestamp(4, picture.getPictimestamp());

			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	@Override
	public List<Date> getPictureDates(String camIds) {

		List<Date> dates = new ArrayList<>();
		Connection connection = null;

		try {

			connection = jndi.getConnection("jdbc/waiDatenbank");

			PreparedStatement ps = connection
					.prepareStatement("SELECT DISTINCT pg_catalog.date(pictimestamp) FROM pictures WHERE camid IN("
							+ camIds + ")");

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				dates.add(rs.getDate(1));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JndiFactory.closeConnection(connection);
		}

		return dates;
	}

}
